import json
from django.http import HttpResponse
from django.shortcuts import render
import sqlite3
import os
import pandas as pd
import joblib

BASE_DIR = os.path.dirname(os.path.abspath(__file__))
DB_PATH = os.path.join(BASE_DIR, 'sql.db')


def query(sql):
    connection = sqlite3.connect(DB_PATH)
    cursor = connection.cursor()
    cursor.execute(sql)
    result = cursor.fetchall()
    connection.close()
    return result


def insert(sql):
    connection = sqlite3.connect(DB_PATH)
    cursor = connection.cursor()
    cursor.execute(sql)
    connection.commit()
    connection.close()


def login(request):
    if request.method == 'GET':
        return render(request, 'login.html')
    else:
        param = json.loads(request.body.decode('utf-8'))
        username = param['username']
        password = param['password']
        sql = 'SELECT * FROM `tb_user` WHERE username = "{0}" AND password = "{1}" LIMIT 0,1'.format(username, password)
        res = query(sql)
        if not res:
            data = '该用户未注册，请注册后再登录'
            return HttpResponse(data)
        else:
            data = "登录成功"
            return HttpResponse(data)


def register(request):
    if request.method == 'GET':
        return render(request, 'register.html')
    else:
        param = json.loads(request.body.decode('utf-8'))
        username = param['username']
        password = param['password']
        sql = 'SELECT * FROM tb_user WHERE username = "{0}" AND password = "{1}" LIMIT 0,1'.format(username, password)
        res = query(sql)
        if res:
            data = '该用户已经注册'
            return HttpResponse(data)
        else:
            sql = 'INSERT INTO tb_user(`username`, `password`) VALUES ("{0}", "{1}")'.format(username, password)
            insert(sql)
            data = "注册成功"
            return HttpResponse(data)


def look_data(request):
    df = pd.read_csv('new.csv')
    # 将 DataFrame 转换为列表
    data_list = df.values.tolist()
    data = []
    for i in data_list:
        a = {
            'Age': i[0],
            'Gender': i[1],
            'Polyuria': i[2],
            'Polydipsia': i[3],
            'sudden_weight_loss': i[4],  # 替换空格为下划线
            'weakness': i[5],
            'Polyphagia': i[6],
            'Genital_thrush': i[7],  # 替换空格为下划线
            'visual_blurring': i[8],  # 替换空格为下划线
            'Itching': i[9],
            'Irritability': i[10],
            'delayed_healing': i[11],  # 替换空格为下划线
            'partial_paresis': i[12],  # 替换空格为下划线
            'muscle_stiffness': i[13],  # 替换空格为下划线
            'Alopecia': i[14],
            'Obesity': i[15],
            'class': i[16]
        }
        data.append(a)
    data = {
        'data': data
    }
    return render(request, 'look_data.html', data)


def visualization(request):
    if request.method == 'GET':
        return render(request, 'visualization.html')
    else:
        result = []
        df = pd.read_csv('new.csv')

        # 根据肥胖状况和糖尿病分类进行分组，并计算数量
        result_counts = df.groupby(['Obesity', 'class']).size().unstack().fillna(0)
        obesity_results = list(result_counts.index)
        is_diabetes = list(result_counts['Positive'])
        not_diabetes = list(result_counts['Negative'])
        result.append([obesity_results, is_diabetes, not_diabetes])

        # 根据性别和糖尿病分类进行分组，并计算数量
        result_counts = df.groupby(['Gender', 'class']).size().unstack().fillna(0)
        # 将结果转换为ECharts饼图格式
        echarts_data_male = []
        echarts_data_female = []
        # 遍历结果，将数据转换为ECharts格式
        for gender in result_counts.index:
            diabetes_count = int(result_counts.loc[gender, 'Positive']) if 'Positive' in result_counts.columns else 0
            non_diabetes_count = int(result_counts.loc[gender, 'Negative']) if 'Negative' in result_counts.columns else 0

            if gender == 'Male':
                echarts_data_male.append({'name': '患病', 'value': diabetes_count})
                echarts_data_male.append({'name': '健康', 'value': non_diabetes_count})
            elif gender == 'Female':
                echarts_data_female.append({'name': '患病', 'value': diabetes_count})
                echarts_data_female.append({'name': '健康', 'value': non_diabetes_count})
        result.append([echarts_data_male, echarts_data_female])

        ageresult1 = df[df['class'] == 'Positive'].groupby('Age').size().reset_index(name='人数').sort_values(
            by='Age').values.tolist()
        ageresult2 = df[df['class'] == 'Negative'].groupby('Age').size().reset_index(name='人数').sort_values(
            by='Age').values.tolist()
        result.append([ageresult1, ageresult2])

        # 使用多尿症状(Polyuria)替代高血压史
        result_counts = df.groupby(['Polyuria', 'class']).size().unstack().fillna(0)
        # 提取结果中的列名和对应的值
        values_have_polyuria = result_counts.loc['Yes'].values.tolist()
        values_no_polyuria = result_counts.loc['No'].values.tolist()
        result.append([values_no_polyuria, values_have_polyuria])
        return HttpResponse(json.dumps(result))


def yc(request):
    if request.method == 'GET':
        return render(request, 'yc.html')
    else:
        # 获取表单数据 - 适配修改后的前端表单
        a1 = request.POST.get('Gender')  # 性别: 1-男, 0-女
        a2 = request.POST.get('Age')  # 年龄
        a3 = request.POST.get('Polyuria')  # 多尿症: 1-是, 0-否
        a4 = request.POST.get('Polydipsia')  # 多饮症: 1-是, 0-否
        a5 = request.POST.get('sudden_weight_loss')  # 突然体重减轻: 1-是, 0-否
        a6 = request.POST.get('weakness')  # 虚弱: 1-是, 0-否
        a7 = request.POST.get('Polyphagia')  # 多食症: 1-是, 0-否
        a8 = request.POST.get('Genital_thrush')  # 生殖器鹅口疮: 1-是, 0-否
        a9 = request.POST.get('visual_blurring')  # 视力模糊: 1-是, 0-否
        a10 = request.POST.get('Itching')  # 瘙痒: 1-是, 0-否
        a11 = request.POST.get('Irritability')  # 易怒: 1-是, 0-否
        a12 = request.POST.get('delayed_healing')  # 伤口愈合延迟: 1-是, 0-否
        a13 = request.POST.get('partial_paresis')  # 部分麻痹: 1-是, 0-否
        a14 = request.POST.get('muscle_stiffness')  # 肌肉僵硬: 1-是, 0-否
        a15 = request.POST.get('Alopecia')  # 脱发: 1-是, 0-否
        a16 = request.POST.get('Obesity')  # 肥胖: 1-是, 0-否

        # 转换为整数类型
        try:
            age = int(a2)
            gender_num = int(a1)
            polyuria_num = int(a3)
            polydipsia_num = int(a4)
            sudden_weight_loss_num = int(a5)
            weakness_num = int(a6)
            polyphagia_num = int(a7)
            genital_thrush_num = int(a8)
            visual_blurring_num = int(a9)
            itching_num = int(a10)
            irritability_num = int(a11)
            delayed_healing_num = int(a12)
            partial_paresis_num = int(a13)
            muscle_stiffness_num = int(a14)
            alopecia_num = int(a15)
            obesity_num = int(a16)
        except (ValueError, TypeError):
            return HttpResponse('输入数据格式有误，请检查后重新提交')

        try:
            loaded_model = joblib.load('static/lgb_model.joblib')
        except FileNotFoundError:
            return HttpResponse('模型文件不存在，请检查路径')
        
        # 准备模型输入数据 - 确保特征名称与训练时完全一致
        simulated_data = {
            'Age': [age],
            'Gender': [gender_num],
            'Polyuria': [polyuria_num],
            'Polydipsia': [polydipsia_num],
            'sudden weight loss': [sudden_weight_loss_num],
            'weakness': [weakness_num],
            'Polyphagia': [polyphagia_num],
            'Genital thrush': [genital_thrush_num],
            'visual blurring': [visual_blurring_num],
            'Itching': [itching_num],
            'Irritability': [irritability_num],
            'delayed healing': [delayed_healing_num],
            'partial paresis': [partial_paresis_num],
            'muscle stiffness': [muscle_stiffness_num]
            # 暂时移除 'Alopecia' 和 'Obesity'
        }

        simulated_df = pd.DataFrame(simulated_data)
        
        # 打印特征列名，用于调试
        print("模型输入特征:", simulated_df.columns.tolist())
        print("特征数量:", len(simulated_df.columns))

        # 使用加载的模型进行预测
        try:
            predictions = loaded_model.predict(simulated_df)[0]
            # 获取预测的概率值
            pred_proba = loaded_model.predict_proba(simulated_df)[0]
            risk_probability = round(pred_proba[1] * 100, 2)  # 获取患病的概率并转换为百分比，保留两位小数
        except Exception as e:
            print(f"预测错误: {e}")
            # 如果上面的方法失败，尝试设置predict_disable_shape_check=true
            try:
                # 注意：这种方法可能不适用于所有LightGBM版本
                # 如果这种方法也失败，您可能需要重新训练模型
                loaded_model.set_params(predict_disable_shape_check=True)
                predictions = loaded_model.predict(simulated_df)[0]
                pred_proba = loaded_model.predict_proba(simulated_df)[0]
                risk_probability = round(pred_proba[1] * 100, 2)
            except Exception as e2:
                return HttpResponse(f'预测过程中出错: {str(e)}\n尝试替代方法也失败: {str(e2)}')
        
        if predictions == 1:
            return HttpResponse(f'！！请注意当前患病风险较高（风险概率：{risk_probability}%）\n请保持良好生活习惯并及时就医')
        return HttpResponse(f'当前患病风险较低（风险概率：{risk_probability}%）\n请继续保持良好生活习惯')

def echarts(request):
    if request.method == 'GET':
        return render(request, 'echarts.html')
    else:
        result = []
        df = pd.read_csv('new.csv')

        # 选择前5个症状进行分析
        symptoms = ['Polyuria', 'Polydipsia', 'sudden weight loss', 'weakness', 'Polyphagia']
        symptom_counts_positive = []
        symptom_counts_negative = []
        
        for symptom in symptoms:
            positive_yes = len(df[(df['class'] == 'Positive') & (df[symptom] == 'Yes')])
            negative_yes = len(df[(df['class'] == 'Negative') & (df[symptom] == 'Yes')])
            symptom_counts_positive.append(positive_yes)
            symptom_counts_negative.append(negative_yes)
        
        result.append([symptoms, symptom_counts_positive, symptom_counts_negative])

        # 根据性别和糖尿病分类进行分组，并计算数量
        result_counts = df.groupby(['Gender', 'class']).size().unstack().fillna(0)
        # 将结果转换为ECharts饼图格式
        echarts_data_male = []
        echarts_data_female = []
        # 遍历结果，将数据转换为ECharts格式
        for gender in result_counts.index:
            diabetes_count = int(result_counts.loc[gender, 'Positive']) if 'Positive' in result_counts.columns else 0
            non_diabetes_count = int(result_counts.loc[gender, 'Negative']) if 'Negative' in result_counts.columns else 0

            if gender == 'Male':
                echarts_data_male.append({'name': '患病', 'value': diabetes_count})
                echarts_data_male.append({'name': '健康', 'value': non_diabetes_count})
            elif gender == 'Female':
                echarts_data_female.append({'name': '患病', 'value': diabetes_count})
                echarts_data_female.append({'name': '健康', 'value': non_diabetes_count})
        result.append([echarts_data_male, echarts_data_female])

        ageresult1 = df[df['class'] == 'Positive'].groupby('Age').size().reset_index(name='人数').sort_values(
            by='Age').values.tolist()
        ageresult2 = df[df['class'] == 'Negative'].groupby('Age').size().reset_index(name='人数').sort_values(
            by='Age').values.tolist()
        result.append([ageresult1, ageresult2])

        # 多尿症状分析
        polyuria_positive_yes = len(df[(df['class'] == 'Positive') & (df['Polyuria'] == 'Yes')])
        polyuria_positive_no = len(df[(df['class'] == 'Positive') & (df['Polyuria'] == 'No')])
        polyuria_negative_yes = len(df[(df['class'] == 'Negative') & (df['Polyuria'] == 'Yes')])
        polyuria_negative_no = len(df[(df['class'] == 'Negative') & (df['Polyuria'] == 'No')])
        
        result.append([
            [polyuria_positive_yes, polyuria_positive_no],
            [polyuria_negative_yes, polyuria_negative_no]
        ])
        
        # 添加视力模糊分析数据
        visual_blurring_positive_yes = len(df[(df['class'] == 'Positive') & (df['visual blurring'] == 'Yes')])
        visual_blurring_positive_no = len(df[(df['class'] == 'Positive') & (df['visual blurring'] == 'No')])
        visual_blurring_negative_yes = len(df[(df['class'] == 'Negative') & (df['visual blurring'] == 'Yes')])
        visual_blurring_negative_no = len(df[(df['class'] == 'Negative') & (df['visual blurring'] == 'No')])
        
        result.append([
            [visual_blurring_positive_yes, visual_blurring_positive_no],
            [visual_blurring_negative_yes, visual_blurring_negative_no]
        ])
        
        return HttpResponse(json.dumps(result))
